﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ufolder
{
    public delegate string SqlGenProc(params string[][] args);

    class ParserException : Exception
    {
      /*  public ParserException() : base()
        {
            
        }*/
    }

    class SQLGenerator
    {
        private string columns = "id, uri, quand, type, titre, texte";
        private string _r;
        private System.Collections.Generic.Stack<SQLGen> process = new Stack<SQLGen>();

        private string sqlBase = "";

        public string ContainText(params string[][] s)
        {
            string[] S = s[0];
            int nbrWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE id IN (SELECT rowid FROM items_c WHERE items_c MATCH '";
            for (int i = 0; i < nbrWord; i++)
            {
                sql += " " + (S[i].Contains('-') ? '"' + S[i] + '"' : S[i]);
            }
            
            sql+="')";
            return sql;
        }

        public string TitreContains(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "id IN (SELECT rowid FROM items_c WHERE titre MATCH '" + (Word[0].Contains('-') ? '"' + Word[0] + '"' : Word[0]);
                for (int i = 1; i < L_Word; i++)
                {
                    sql += " AND " + (Word[i].Contains('-') ? '"' + Word[i] + '"' : Word[i]);
                }

                sql += "')";
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " AND ";

                sql += "id NOT IN (SELECT rowid FROM items_c WHERE titre MATCH '" + (NWord[0].Contains('-') ? '"' + NWord[0] + '"' : NWord[0]);
                for (int i = 1; i < L_NWord; i++)
                {
                    sql += " AND " + (NWord[i].Contains('-') ? '"' + NWord[i] + '"' : NWord[i]);
                }

                sql += "')";
            }
            return sql;
        }

        public string TexteContains(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "id IN (SELECT rowid FROM items_c WHERE texte MATCH '"; //TODO "-"
                for (int i = 0; i < L_Word; i++)
                {
                    sql += " " + (Word[i].Contains('-') ? '"' + Word[i] + '"' : Word[i]);
                }

                sql += "')";
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " AND ";

                sql += "id NOT IN (SELECT rowid FROM items_c WHERE texte MATCH '";
                for (int i = 0; i < L_NWord; i++)
                {
                    sql += " " + (NWord[i].Contains('-') ? '"' + NWord[i] + '"' : NWord[i]);
                }

                sql += "')";
            }
            return sql;
        }

        public string TitreContainsOr(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "id IN (SELECT rowid FROM items_c WHERE titre MATCH '" + (Word[0].Contains('-') ? '"' + Word[0] + '"' : Word[0]);
                for (int i = 1; i < L_Word; i++)
                {
                    sql += " OR " + (Word[i].Contains('-') ? '"' + Word[i] + '"' : Word[i]);
                }

                sql += "')";
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " OR ";

                sql += "id NOT IN (SELECT rowid FROM items_c WHERE titre MATCH '" + (NWord[0].Contains('-') ? '"' + NWord[0] + '"' : NWord[0]);
                for (int i = 1; i < L_NWord; i++)
                {
                    sql += " OR " + (NWord[i].Contains('-') ? '"' + NWord[i] + '"' : NWord[i]);
                }

                sql += "')";
            }
            return sql;
        }

        public string TypeContains(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "type = '" + Word[0] + "'";
                for (int i = 1; i < L_Word; i++)
                {
                    sql += " AND type = '" + Word[i] + "'";
                }
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " AND ";

                sql += "type != '" + NWord[0] + "'";
                for (int i = 1; i < L_NWord; i++)
                {
                    sql += " AND type != '" + NWord[i] + "'";
                }
            }
            return sql;
        }
        public string TexteContainsOr(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "id IN (SELECT rowid FROM items_c WHERE texte MATCH '" + (Word[0].Contains('-') ? '"' + Word[0] + '"' : Word[0]);
                for (int i = 1; i < L_Word; i++)
                {
                    sql += " OR " + (Word[i].Contains('-') ? '"' + Word[i] + '"' : Word[i]);
                }

                sql += "')";
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " OR ";

                sql += "id NOT IN (SELECT rowid FROM items_c WHERE texte MATCH '" + (NWord[0].Contains('-') ? '"' + NWord[0] + '"' : NWord[0]);
                for (int i = 1; i < L_NWord; i++)
                {
                    sql += " OR " + (NWord[i].Contains('-') ? '"' + NWord[i] + '"' : NWord[i]);
                }

                sql += "')";
            }
            return sql;
        }

        public string TypeContainsOr(params string[][] s)
        {
            string[] Word = s[1]; int L_Word = s[1].Length;
            string[] NWord = s[0]; int L_NWord = s[0].Length;

            string sql = "SELECT " + columns + " FROM items WHERE ";

            if (L_Word > 0)
            {
                sql += "type = '" + Word[0] + "'";
                for (int i = 1; i < L_Word; i++)
                {
                    sql += " OR type = '" + Word[i] + "'";
                }
            }

            if (L_NWord > 0)
            {
                if (L_Word > 0)
                    sql += " OR ";

                sql += "type != '" + NWord[0] + "'";
                for (int i = 1; i < L_NWord; i++)
                {
                    sql += " OR type != '" + NWord[i] + "'";
                }
            }
            return sql;
        }

        public string ContainLabels(params string[][] s)
        {
            string[] Label = s[3]; int L_Label = Label.Length;
            string[] LGroup = s[2]; int L_LGroup = LGroup.Length;
            string[] NLabel = s[1]; int L_NLabel = NLabel.Length;
            string[] NLGroup = s[0]; int L_NLGroup = NLGroup.Length;

            string sql = "SELECT " + columns + " FROM items i WHERE ";

            // On s'occupe des Label:
            if (L_Label > 0)
            {
                if (L_Label == 1)
                {
                    sql += "EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id and label='" + Label[0] + "')";
                }
                else
                {
                    sql += "(SELECT count(id) FROM items_w_tags WHERE id=i.id and label in ('" + Label[0] + "'";

                       for (int i = 1; i < L_Label; i++)
                            sql += ", '" + Label[i] + "'";

                       sql += "))=" + L_Label;
                }
            }

            //On s'occupe des NLabel
            //TODO: -<za -zu fonctionne pas => S'occuper des NLabel comme d'une group a un seul élément !
            if (L_NLabel > 0)
            {
                if (L_Label != 0)
                    sql += " AND ";

                if (L_NLabel == 1)
                {
                    sql += "NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id and label='" + NLabel[0] + "')";
                }
                else
                {
                    sql += "(SELECT count(id) FROM items_w_tags WHERE id=i.id and label in ('" + NLabel[0] + "'";

                    for (int i = 1; i < L_NLabel; i++)
                    {
                        sql += ", '" + NLabel[i] + "'";
                    }
                    sql += "))=0";
                }
            }

            //On s'occupe des Groupes
            if (L_LGroup > 0)
            {
                if (L_Label != 0 || L_NLabel != 0)
                    sql += " AND ";

                sql += "EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + LGroup[0] + "' UNION SELECT '" + LGroup[0] + "' as label))";

                for (int i = 1; i < L_LGroup; i++)
                {
                    sql += " AND EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + LGroup[i] + "' UNION SELECT '" + LGroup[i] + "' as label))";
                }
            }

            if (L_NLGroup > 0)
            {
                if (L_Label != 0 || L_NLabel != 0 || L_LGroup != 0)
                    sql += " AND ";
                
                sql += "NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + NLGroup[0] + "' UNION SELECT '" + NLGroup[0] + "' as label))";

                for (int i = 1; i < L_NLGroup; i++)
                    sql += " AND NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + NLGroup[i] + "' UNION SELECT '" + NLGroup[i] + "' as label))";
            }
            return sql;
        }

        public string ContainAnyLabels(params string[][] s)
        {
            string[] Label = s[3]; int L_Label = Label.Length;
            string[] LGroup = s[2]; int L_LGroup = LGroup.Length;
            string[] NLabel = s[1]; int L_NLabel = NLabel.Length;
            string[] NLGroup = s[0]; int L_NLGroup = NLGroup.Length;

            string sql = "SELECT " + columns + " FROM items i WHERE ";

            if (L_Label > 0)
            {
                if (L_Label == 1)
                {
                    sql += "EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id and label='" + Label[0] + "')";
                }
                else
                {
                    sql += "EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id and label IN('" + Label[0] + "'";

                    for (int i = 1; i < L_Label; i++)
                        sql += ", '" + Label[i] + "'";

                    sql += "))";
                }
            }

            if (L_NLabel > 0)
            {
                if (L_Label != 0)
                    sql += " OR ";

                if (L_NLabel == 1)
                {
                    sql += "NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id and label='" + NLabel[0] + "')";
                }
                else
                {
                    sql += "(SELECT count(id) FROM items_w_tags WHERE id=i.id and label in ('" + NLabel[0] + "'";

                    for (int i = 1; i < L_NLabel; i++)
                    {
                        sql += ", '" + NLabel[i] + "'";
                    }
                    sql += "))!=" + L_NLabel;
                }
            }

            //On s'occupe des Groupes
            if (L_LGroup > 0)
            {
                if (L_Label != 0 || L_NLabel != 0)
                    sql += " OR ";

                sql += "EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + LGroup[0] + "' UNION SELECT '" + LGroup[0] + "' as label))";

                for (int i = 1; i < L_LGroup; i++)
                {
                    sql += " OR EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + LGroup[i] + "' UNION SELECT '" + LGroup[i] + "' as label))";
                }
            }

            if (L_NLGroup > 0)
            {
                if (L_Label != 0 || L_NLabel != 0 || L_LGroup != 0)
                    sql += " OR ";

                sql += "NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + NLGroup[0] + "' UNION SELECT '" + NLGroup[0] + "' as label))";

                for (int i = 1; i < L_NLGroup; i++)
                    sql += " OR NOT EXISTS (SELECT 'X' FROM items_w_tags WHERE id=i.id AND label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + NLGroup[i] + "' UNION SELECT '" + NLGroup[i] + "' as label))";
            }

            return sql;
        }

        public string Intersect(params string[][] s)
        {
            //string[] S = s[0];
            string SQLAll = "SELECT " + columns + " FROM items";
            
            switch (s.Length)
            {
                case 0: return SQLAll;
                case 1: return s[0][0];
                default:
                    string sql = "SELECT * FROM (" + s[0][0];

                    int sNumber = s.Length;
                    for (int i = 1; i < sNumber; i++)
                    {
                        sql += " INTERSECT " + s[i][0];
                    }
                    sql += ")";
                    return sql;
            }
        }

        public string HasAll(params string[][] s)
        {
            string[] HasNot = s[1]; int L_HasNot = HasNot.Length;
            string[] Has = s[0]; int L_Has = Has.Length;

            string sql = "SELECT " + columns + " FROM ITEMS WHERE ";

            if (L_Has > 0)
            {
                if (Has[0].ToLower() != "tag")
                    sql += Has[0] + " IS NOT NULL";
                else
                    sql += "id IN (SELECT items_tags.id FROM items_tags)";

                for (int i = 1; i < L_Has; i++)
                {
                    if (Has[i].ToLower() != "tag")
                        sql += " AND " + Has[i] + " IS NOT NULL";
                    else
                        sql += " AND id IN (SELECT items_tags.id FROM items_tags)";
                }

            }

            if (L_HasNot > 0)
            {
                if (L_Has > 0)
                    sql += " AND ";

                if (HasNot[0].ToLower() != "tag")
                    sql += HasNot[0] + " IS NULL";
                else
                    sql += "id NOT IN (SELECT items_tags.id FROM items_tags)";

                for (int i = 1; i < L_HasNot; i++)
                {
                    if (HasNot[i].ToLower() != "tag")
                        sql += " AND " + HasNot[i] + " IS NULL";
                    else
                        sql += " AND id NOT IN (SELECT items_tags.id FROM items_tags)";
                }

            }

            return sql;
        }

        public string HasAny(params string[][] s)
        {
            string[] HasNot = s[1]; int L_HasNot = HasNot.Length;
            string[] Has = s[0]; int L_Has = Has.Length;

            string sql = "SELECT " + columns + " FROM ITEMS WHERE ";

            if (L_Has > 0)
            {
                if (Has[0].ToLower() != "tag")
                    sql += Has[0] + " IS NOT NULL";
                else
                    sql += "id IN (SELECT items_tags.id FROM items_tags)";

                for (int i = 1; i < L_Has; i++)
                {
                    if (Has[i].ToLower() != "tag")
                        sql += " OR " + Has[i] + " IS NOT NULL";
                    else
                        sql += " OR id IN (SELECT items_tags.id FROM items_tags)";
                }

            }

            if (L_HasNot > 0)
            {
                if (L_Has > 0)
                    sql += " OR ";

                if (HasNot[0].ToLower() != "tag")
                    sql += HasNot[0] + " IS NULL";
                else
                    sql += "id NOT IN (SELECT items_tags.id FROM items_tags)";

                for (int i = 1; i < L_HasNot; i++)
                {
                    if (HasNot[i].ToLower() != "tag")
                        sql += " OR " + HasNot[i] + " IS NULL";
                    else
                        sql += " OR id NOT IN (SELECT items_tags.id FROM items_tags)";
                }

            }

            return sql;
        }

        public string Union(params string[][] s)
        {
            //string[] S = s[0];
            string SQLAll = "SELECT " + columns + " FROM items";

            switch (s.Length)
            {
                case 0: return SQLAll;
                case 1: return s[0][0];
                default:
                    string sql = "SELECT * FROM (" + s[0][0];

                    int sNumber = s.Length;
                    for (int i = 1; i < sNumber; i++)
                    {
                        sql += " UNION " + s[i][0];
                    }
                    sql += ")";
                    return sql;
            }
        }

        public string Complemente(params string[][] s)
        {
            if (s.Length != 0)
            {
                string sql = "SELECT * FROM (SELECT " + columns + " FROM items EXCEPT " + s[0][0] + ")";
                return sql;
            }
            else throw new ParserException();
        }

        public string BeforeDate(params string[][] s)
        {
            string sql = "SELECT " + columns + " FROM items WHERE items.quand < '" + s[0][0] + "'";
            return sql;
        }

        public string AfterDate(params string[][] s)
        {
            string sql = "SELECT " + columns + " FROM items WHERE items.quand > '" + s[0][0] + "'";
            return sql;
        }

        public string SavedAt(params string[][] s)
        {
            if (s.Length == 0) throw new ParserException();
            string sql = "SELECT " + columns + " FROM items WHERE items.quand IN ('" + s[0][0] + "'";

            int nbrDate = s.Length;
            for(int i=1; i<nbrDate; i++)
            {
                sql += ", '" + s[i][0] + "' ";
            }
            return sql += ')';
        }

        public string SQL
        {
            get
            {
                return sqlBase + this._r;
            }
        }

        public void ProcessQueue()
        {
            SQLGen sqlgen = process.Pop();
            this._r = sqlgen.Proc(sqlgen.Arg.ToArray());
        }

        public void AddParam(Token s)
        {
            this.AddParam(new string[] { s.ToString() });
        }


        public void AddParam(ArrayList s)
        {
            this.AddParam((string[])s.ToArray(typeof (string)));
        }

        public void AddParam(string[] s)
        {
            this.process.Peek().Arg.Push(s);
        }

        public void AddProc(SqlGenProc p)
        {
            this.process.Push(new SQLGen(p));
        }

        public void ParamPropag()
        {
            if (process.Count != 0)
                process.Peek().Arg.Push(new string[] { this._r });
            else
                throw new ParserException();
        }

    }


    class SQLGen
    {
        public readonly SqlGenProc Proc;
        public System.Collections.Generic.Stack<string[]> Arg = new Stack<string[]>();

        public SQLGen(SqlGenProc d)
        {
            this.Proc = d;
        }
    }
}
